Visualizing rental listings

In [1]:
%matplotlib inline

import geopandas as gpd
import pandas as pd
import numpy as np
import pysal as ps
import folium

from shapely.geometry import Point

from folium import IFrame
from folium.plugins import MarkerCluster

Read the rental listings prepared and saved earlier

In [2]:
rents_df_new = pd.read_csv('./data/rent/geocoded_rents.csv', sep=',', encoding='utf-8')
In [3]:
rents_df_new.head()
Out[3]:
Unnamed: 0 ID Haus/Wohnung ID.1 Miete/Kauf address balcony builtInKitchen city floorplan ... numberOfRooms postcode price privateOffer quarter title url price_sq_m lat1 lon1
0 0 100027976 Wohnung 100027976.0 Miete Pohlestraße 14, Köpenick (Köpenick), Berlin True True Berlin True ... 3.0 12557.0 828.00 False Köpenick (Köpenick) 3 RaumWohnung; Balkon +Dach Garten Anteil + EB... https://www.immobilienscout24.de/expose/100027976 9.975904 52.44056 13.58415
1 1 100122011 Wohnung 100122011.0 Miete Soldiner Straße 35, Wedding (Wedding), Berlin False False Berlin False ... 2.0 13359.0 1086.00 False Wedding (Wedding) !!! DREI NETTOKALTMIETEN FREI !!! - Große 2-Zi... https://www.immobilienscout24.de/expose/100122011 12.998205 52.55992 13.38284
2 2 100149210 Wohnung 100149210.0 Miete Vopeliuspfad 10, Zehlendorf (Zehlendorf), Berlin True True Berlin True ... 6.0 14169.0 2760.00 False Zehlendorf (Zehlendorf) ++Eindrucksvolle Altbauwohnung im 2. OG mit 3 ... https://www.immobilienscout24.de/expose/100149210 11.596639 52.43492 13.26728
3 3 100150862 Wohnung 100150862.0 Miete Rudolf-von-Gneist-Gasse 1, Tiergarten (Tiergar... True True Berlin True ... 2.0 10785.0 1636.00 False Tiergarten (Tiergarten) #Skylights: Dachgeschossmaisonette mit großer ... https://www.immobilienscout24.de/expose/100150862 16.007828 52.50677 13.37421
4 4 100155473 Wohnung 100155473.0 Miete Wackenbergstr. 57, Niederschönhausen (Pankow),... True True Berlin True ... 2.0 13156.0 636.76 True Niederschönhausen (Pankow) Schön Wohnen im Erdgeschoss mit Terrasse und "... https://www.immobilienscout24.de/expose/100155473 8.496931 52.58621 13.41376

5 rows × 23 columns

In [4]:
rents_df_new.loc[pd.isna(rents_df_new['lat1'])].shape
Out[4]:
(0, 23)

Convert data into a Geodataframe

In [5]:
rents_geom = [Point(xy) for xy in zip(rents_df_new.lon1, rents_df_new.lat1)]

rents_df_temp = rents_df_new.drop(['lat1', 'lon1'], axis = 1)
crs = {'init': 'epsg:4326'}

rents_gdf = gpd.GeoDataFrame(rents_df_temp, crs=crs, geometry=rents_geom)
In [6]:
rents_gdf.plot()
Out[6]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f685152f9d0>
In [7]:
rents_gdf.shape
Out[7]:
(3262, 22)

Read in the shape file for Berlin admin regions

In [9]:
berlin_file = "./data/Berlin__RBSOrtsteile"

berlin_df = gpd.read_file(berlin_file, encoding='utf-8')

print(type(berlin_df))

berlin_df
<class 'geopandas.geodataframe.GeoDataFrame'>
Out[9]:
OBJECTID ORT Ortsteilna Beznr Bezname geometry
0 1 1102 Karlshorst 11 Lichtenberg POLYGON ((13.5058337078815 52.4911663667287, 1...
1 2 0312 Rosenthal 03 Pankow POLYGON ((13.3748609896621 52.5849015535632, 1...
2 3 0706 Lichtenrade 07 Tempelhof-Schöneberg POLYGON ((13.3963154428129 52.4175789962893, 1...
3 4 0405 Westend 04 Charlottenburg-Wilmersdorf POLYGON ((13.2874646673229 52.5023678928998, 1...
4 5 1001 Marzahn 10 Marzahn-Hellersdorf POLYGON ((13.5185982721988 52.5145347292375, 1...
5 6 0103 Hansaviertel 01 Mitte POLYGON ((13.3342146763547 52.5167714412256, 1...
6 7 0905 Niederschöneweide 09 Treptow-Köpenick POLYGON ((13.5575320543165 52.4530395205999, 1...
7 8 0502 Haselhorst 05 Spandau POLYGON ((13.2318117950734 52.5343267290102, 1...
8 9 0703 Tempelhof 07 Tempelhof-Schöneberg POLYGON ((13.3661791389537 52.4459918385382, 1...
9 10 0509 Wilhelmstadt 05 Spandau POLYGON ((13.158586922754 52.5129384952068, 13...
10 11 0309 Buch 03 Pankow (POLYGON ((13.5230231109803 52.6450341927487, ...
11 12 0803 Buckow 08 Neukölln (POLYGON ((13.4468548026313 52.4267989365331, ...
12 13 0403 Schmargendorf 04 Charlottenburg-Wilmersdorf POLYGON ((13.3092533404718 52.4815299390291, 1...
13 14 1004 Mahlsdorf 10 Marzahn-Hellersdorf POLYGON ((13.5863460838916 52.4811162379197, 1...
14 15 0301 Prenzlauer Berg 03 Pankow POLYGON ((13.3974960428953 52.5549678174287, 1...
15 16 0505 Gatow 05 Spandau POLYGON ((13.1355511023264 52.485406118845, 13...
16 17 0106 Gesundbrunnen 01 Mitte POLYGON ((13.3883996367513 52.5676684290514, 1...
17 18 1208 Lübars 12 Reinickendorf POLYGON ((13.3435710064329 52.6234152236302, 1...
18 19 0902 Plänterwald 09 Treptow-Köpenick POLYGON ((13.492485396733 52.470218530443, 13....
19 20 1109 Neu-Hohenschönhausen 11 Lichtenberg POLYGON ((13.510015470632 52.5789846176516, 13...
20 21 0607 Wannsee 06 Steglitz-Zehlendorf POLYGON ((13.1618609343166 52.4521923976217, 1...
21 22 0704 Mariendorf 07 Tempelhof-Schöneberg POLYGON ((13.4112582213699 52.4391231533446, 1...
22 23 0310 Französisch Buchholz 03 Pankow POLYGON ((13.4365277674301 52.5875785609254, 1...
23 24 0804 Rudow 08 Neukölln POLYGON ((13.476581785538 52.4297466790672, 13...
24 25 0404 Grunewald 04 Charlottenburg-Wilmersdorf POLYGON ((13.2097268512555 52.5029102150093, 1...
25 26 0101 Mitte 01 Mitte POLYGON ((13.373622257801 52.5279175137604, 13...
26 27 0911 Friedrichshagen 09 Treptow-Köpenick POLYGON ((13.6254260677201 52.4442533662631, 1...
27 28 1005 Hellersdorf 10 Marzahn-Hellersdorf POLYGON ((13.576494688667 52.5122726455331, 13...
28 29 0304 Heinersdorf 03 Pankow POLYGON ((13.4575048684154 52.5755828816744, 1...
29 30 0602 Lichterfelde 06 Steglitz-Zehlendorf POLYGON ((13.3429578218098 52.4118121059253, 1...
... ... ... ... ... ... ...
66 67 1204 Heiligensee 12 Reinickendorf POLYGON ((13.2181183116669 52.5931932561776, 1...
67 68 0313 Wilhelmsruh 03 Pankow POLYGON ((13.3606485216681 52.59126796106, 13....
68 69 0603 Lankwitz 06 Steglitz-Zehlendorf POLYGON ((13.3691928376116 52.4330737014886, 1...
69 70 0702 Friedenau 07 Tempelhof-Schöneberg POLYGON ((13.3199817447386 52.4669799765668, 1...
70 71 1210 Märkisches Viertel 12 Reinickendorf POLYGON ((13.3754427224704 52.6065269076014, 1...
71 72 1104 Falkenberg 11 Lichtenberg POLYGON ((13.5300298507205 52.5705967045503, 1...
72 73 0402 Wilmersdorf 04 Charlottenburg-Wilmersdorf POLYGON ((13.3092533404718 52.4815299390291, 1...
73 74 0802 Britz 08 Neukölln POLYGON ((13.4112582213699 52.4391231533446, 1...
74 75 0306 Stadtrandsiedlung Malchow 03 Pankow POLYGON ((13.4854991969718 52.5873476844527, 1...
75 76 0915 Schmöckwitz 09 Treptow-Köpenick POLYGON ((13.6063945397738 52.379118425093, 13...
76 77 1205 Frohnau 12 Reinickendorf POLYGON ((13.2838383086581 52.6411170789289, 1...
77 78 0105 Wedding 01 Mitte POLYGON ((13.3609251246222 52.5377235899816, 1...
78 79 0907 Adlershof 09 Treptow-Köpenick POLYGON ((13.5634168764203 52.4267153533601, 1...
79 80 0504 Staaken 05 Spandau POLYGON ((13.1541547249736 52.514438763481, 13...
80 81 0914 Müggelheim 09 Treptow-Köpenick POLYGON ((13.6313953664949 52.4018386973946, 1...
81 82 0604 Zehlendorf 06 Steglitz-Zehlendorf POLYGON ((13.2383340518213 52.4691691681703, 1...
82 83 1101 Friedrichsfelde 11 Lichtenberg POLYGON ((13.5086719251402 52.5143868829641, 1...
83 84 0705 Marienfelde 07 Tempelhof-Schöneberg POLYGON ((13.3458301058791 52.4151962097873, 1...
84 85 1107 Wartenberg 11 Lichtenberg POLYGON ((13.4854991969718 52.5873476844527, 1...
85 86 0901 Alt-Treptow 09 Treptow-Köpenick POLYGON ((13.4396578060021 52.4899084647742, 1...
86 87 0102 Moabit 01 Mitte POLYGON ((13.3609251246222 52.5377235899816, 1...
87 88 0910 Köpenick 09 Treptow-Köpenick POLYGON ((13.5634168764203 52.4267153533601, 1...
88 89 0303 Blankenburg 03 Pankow POLYGON ((13.4575048684154 52.5755828816744, 1...
89 90 1211 Borsigwalde 12 Reinickendorf POLYGON ((13.3058488953266 52.5919032765882, 1...
90 91 0601 Steglitz 06 Steglitz-Zehlendorf POLYGON ((13.3493933237592 52.4441571715259, 1...
91 92 1111 Fennpfuhl 11 Lichtenberg POLYGON ((13.4696072315362 52.5228104358091, 1...
92 93 0202 Kreuzberg 02 Friedrichshain-Kreuzberg POLYGON ((13.4396578060021 52.4899084647742, 1...
93 94 0904 Johannisthal 09 Treptow-Köpenick POLYGON ((13.4789418169946 52.4586590196829, 1...
94 95 1202 Tegel 12 Reinickendorf POLYGON ((13.2209167910507 52.5948806163434, 1...
95 96 0501 Spandau 05 Spandau POLYGON ((13.1916618728257 52.5459943400175, 1...

96 rows × 6 columns

In [10]:
berlin_df.plot()
Out[10]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f684e27de90>
In [12]:
berlin_df.shape
Out[12]:
(96, 6)

Spatial join to find listings for each region

In [11]:
rents_with_districts = gpd.sjoin(berlin_df, rents_gdf)
In [13]:
rents_with_districts.shape
Out[13]:
(3253, 28)

Compute average rent per square meter for each region

In [14]:
avg_rents = gpd.sjoin(berlin_df, rents_gdf).groupby('OBJECTID').agg({'price_sq_m' : np.mean})
In [15]:
rents_with_districts.shape, avg_rents.shape
Out[15]:
((3253, 28), (88, 1))
In [16]:
avg_rents_with_districts = pd.merge(berlin_df, avg_rents, left_on='OBJECTID', right_on='OBJECTID')
In [17]:
avg_rents_with_districts.head()
Out[17]:
OBJECTID ORT Ortsteilna Beznr Bezname geometry price_sq_m
0 1 1102 Karlshorst 11 Lichtenberg POLYGON ((13.5058337078815 52.4911663667287, 1... 10.556429
1 2 0312 Rosenthal 03 Pankow POLYGON ((13.3748609896621 52.5849015535632, 1... 11.903962
2 3 0706 Lichtenrade 07 Tempelhof-Schöneberg POLYGON ((13.3963154428129 52.4175789962893, 1... 9.284803
3 4 0405 Westend 04 Charlottenburg-Wilmersdorf POLYGON ((13.2874646673229 52.5023678928998, 1... 13.092441
4 5 1001 Marzahn 10 Marzahn-Hellersdorf POLYGON ((13.5185982721988 52.5145347292375, 1... 8.177116

Visualization

Simple visualization

In [18]:
avg_rents_with_districts.plot(column='price_sq_m', cmap='OrRd', scheme='Fisher_Jenks')
Out[18]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f684e2dab10>

Interactive visualization

In [45]:
print('Producing folium choropleth and cluster marker visualization for rents')
# Create a Folium basemap centered in Berlin 
rents_map = folium.Map([52.5161,13.3770], zoom_start = 10)

def add_choropleth_to_map(mapobj):

    # Generate the list of threshold values for classification based on a Fisher Jenks classifier
    my_threshold_scale = ps.esda.mapclassify.Fisher_Jenks(avg_rents_with_districts['price_sq_m']).bins.tolist()
    
    # Call the Folium choropleth function, specifying the geometry from the GeoDataFrame converted to GeoJSON 
    # and the data as the GeoDataFrame
    mapobj.choropleth(geo_data = avg_rents_with_districts.to_json(), data = avg_rents_with_districts, \
                      columns = ['OBJECTID', 'price_sq_m'], key_on = 'feature.properties.{}'.format('OBJECTID'), \
                      fill_color = 'YlOrRd', fill_opacity = 0.6, line_opacity = 0.2, \
                      threshold_scale = my_threshold_scale)
    
    for idx, row in avg_rents_with_districts.iterrows():
        popup = row['Ortsteilna'] + "\n" + str(row['price_sq_m'])
        #popup = IFrame(popup_label, width = 300, height = 100)
        folium.CircleMarker([row.geometry.centroid.y, row.geometry.centroid.x], popup=popup,
                        radius=2, color='white').add_to(mapobj)
        
    
    return mapobj

# Add the choropleth to the basemap
rents_map=add_choropleth_to_map(rents_map)
Producing folium choropleth and cluster marker visualization for rents
In [46]:
def add_clusters_to_map(mapobj):
    
    # The location coordinates and the pop-ups for the single activity markers
    coords, popups = [], [] 
    
    # The information to be shown in the marker pop-ups
    popup_fields = ['url', 'price_sq_m']
    
    for i, row in rents_gdf.iterrows():
        # Populate the list of coordinates with the list of activites
        coords.append([row.geometry.y, row.geometry.x])
        
        # Create HTML for the IFrame popup
        label = '<br>'.join([str(row[field]) for field in popup_fields])
        
        # Append the IFrame containing the HTML to the pop-ups list 
        popups.append(IFrame(label, width = 300, height = 100))
        
    # Create a Folium feature group, since we will be displaying multiple layers
    point_layer = folium.FeatureGroup(name = 'point_layer')
    
    # Add the clustered points and pop-ups to this layer
    point_layer.add_child(MarkerCluster(locations = coords, popups = popups))
    
    # Add this point layer to the map object
    mapobj.add_child(point_layer)
    
    return mapobj

# Add the clusters layer to the map 
rents_map = add_clusters_to_map(rents_map)
In [47]:
folium.LayerControl().add_to(rents_map) # Allows layers to be toggled on/off in the map

rents_map.save('rents_map2.html') # Save map as HTML file

print('Saving visualization under: rents_map.html')
Saving visualization under: rents_map.html
In [48]:
rents_map
Out[48]:
In [ ]: